package me.alide.common.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.List;
import java.util.Map;

/**
 * Created by wutairui on 2019/5/30.
 */
public class ExcelUtils {

    public static HSSFWorkbook createWorkBook(List<Map<String, Object>> list, String []keys, String columnNames[]) {
        // 创建excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建第一个sheet页，并命名
        HSSFSheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 设置列宽
        for(int i=0;i<keys.length;i++){
            //最后一列为附件URL地址,列宽设置大一些
            if(i==(keys.length-1)){
                sheet.setColumnWidth((short) i, (short) (200*120));
            }else{
                sheet.setColumnWidth((short) i, (short) (50*60));
            }
        }
        // 创建第一行，并设置其单元格格式
        HSSFRow row = sheet.createRow((short) 0);
        row.setHeight((short)500);
        // 单元格格式(用于列名)
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        f.setFontName("宋体");
        f.setFontHeightInPoints((short) 10);
        f.setBold(true);
        cs.setFont(f);
        cs.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cs.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cs.setLocked(true);
        cs.setWrapText(true);//自动换行
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }

        //设置首行外,每行每列的值(Row和Cell都从0开始)
        for (short i = 1; i < list.size(); i++) {
            HSSFRow row1 = sheet.createRow((short) i);
            String flag = "";
            //在Row行创建单元格
            for(short j=0;j<keys.length;j++){
                HSSFCell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                if(list.get(i).get(keys[j])!=null){
                    if("优".equals(list.get(i).get(keys[j]).toString())){
                        flag = "优";
                    }else if("差".equals(list.get(i).get(keys[j]).toString())) {
                        flag = "差";
                    }
                }
            }
            //设置该行样式
            HSSFFont f2 = wb.createFont();
            f2.setFontName("宋体");
            f2.setFontHeightInPoints((short) 10);

            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFont(f2);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            cellStyle.setLocked(true);
            cellStyle.setWrapText(true);//自动换行
            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());// 设置背景色
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //依次为每个单元格设置样式
            for(int m=0;m<keys.length;m++){
                HSSFCell hssfCell = row1.getCell(m);
                hssfCell.setCellStyle(cellStyle);
            }
        }
        return wb;
    }
}
